﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Configuration;
using System.IO;

namespace BF.Common
{
    public class SqlHelper
    {
        #region 获取连接字符串熟悉
        private static string _connectionString_inner = string.Empty;
        private static string _connectionString
        {
            get
            {
                if (_connectionString_inner == string.Empty)
                {
                    try
                    {
                        _connectionString_inner = ConfigurationManager
                            .ConnectionStrings["HIP_EHREntities"].ToString();
                        _connectionString_inner = _connectionString_inner
                            .Substring(
                            _connectionString_inner.IndexOf("Data Source"),
                            _connectionString_inner.Length - 1 - _connectionString_inner.IndexOf("Data Source")
                            );
                    }
                    catch (Exception)
                    {
                        _connectionString_inner = string.Empty;
                        throw (new Exception("报表数据库连接错误！"));


                    }
                }
                return _connectionString_inner;
            }

        }

        private static string _connectionString_inner2 = string.Empty;
        private static string _connectionStringBaseFramework
        {


            get
            {
                if (_connectionString_inner2 == string.Empty)
                {
                    try
                    {
                        _connectionString_inner2 = ConfigurationManager.ConnectionStrings["WSJD_BaseFrameworkEntities"].ToString();
                        _connectionString_inner2 = _connectionString_inner2.Substring(_connectionString_inner2.IndexOf("Data Source"), _connectionString_inner2.Length - 1 - _connectionString_inner2.IndexOf("Data Source"));
                    }
                    catch (Exception)
                    {
                        _connectionString_inner2 = string.Empty;
                        throw (new Exception("报表数据库连接错误！"));


                    }
                }
                return _connectionString_inner2;
            }

        }

        private static string _connectionString_inner3 = string.Empty;
        private static string _connectionStringHIP_FinanceEntities
        {


            get
            {
                if (_connectionString_inner3 == string.Empty)
                {
                    try
                    {
                        _connectionString_inner3 = ConfigurationManager.ConnectionStrings["HIP_FinanceEntities"].ToString();
                        _connectionString_inner3 = _connectionString_inner3.Substring(_connectionString_inner3.IndexOf("Data Source"), _connectionString_inner3.Length - 1 - _connectionString_inner3.IndexOf("Data Source"));
                    }
                    catch (Exception)
                    {
                        _connectionString_inner3 = string.Empty;
                        throw (new Exception("报表数据库连接错误！"));


                    }
                }
                return _connectionString_inner3;
            }

        }

        #endregion

        #region  执行简单SQL语句

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>bool值，成功返回true，失败返回false</returns>
        public static bool ExecuteSql(string SQLString)
        {
            bool isSucc = false;
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                using (SqlTransaction trans = connection.BeginTransaction())
                {
                    using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                    {
                        try
                        {
                            cmd.Transaction = trans;
                            int rows = cmd.ExecuteNonQuery();
                            trans.Commit();
                            isSucc = true;
                        }
                        catch (System.Data.SqlClient.SqlException e)
                        {
                            trans.Rollback();
                            connection.Close();
                            throw e;
                        }

                    }
                }

            }
            return isSucc;
        }
        #endregion

        #region 用List<T>执行多条sql的事物方法
        public static bool ExecuteTrans(List<string> sqlList, List<SqlParameter[]> paraList)
        {
            bool isSucc = false;
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                SqlTransaction transaction = null;
                cmd.Connection = conn;
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    cmd.Transaction = transaction;

                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        cmd.CommandText = sqlList[i];
                        if (paraList != null && paraList[i] != null)
                        {
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddRange(paraList[i]);
                        }
                        try
                        {
                            cmd.ExecuteNonQuery();

                        }
                        catch (Exception)
                        {

                            throw;
                        }
                    }
                    transaction.Commit();
                    //设置Error标志
                    isSucc = true;

                }
                catch (Exception e)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch
                    {

                    }
                    throw e;
                }

            }
            return isSucc;

        }

        #endregion

        #region 执行存储过程(无输出参数)
        /// <summary>
        /// 执行存储过程(无输出参数)
        /// </summary>
        /// <param name="storedProcedureName">存储过程名</param>
        /// <param name="p">参数(允许0个或者0个以上)</param>
        public static DataTable ExecuteStoredProcedure(string storedProcedureName, params SqlParameter[] p)
        { //获得返回集实例
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {

                SqlCommand comm = new SqlCommand();
                comm.CommandText = storedProcedureName;
                comm.CommandType = CommandType.StoredProcedure;
                //设置参数
                if (p != null && p.Length > 0)
                {
                    foreach (SqlParameter parameter in p)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        comm.Parameters.Add(parameter);
                    }
                }
                comm.Connection = conn;
                SqlDataAdapter da = new SqlDataAdapter(comm);
                //try
                //{
                //填充数据
                da.Fill(dt);
            }
            return dt;
        }

        public static DataTable ExecuteStoredProcedure(string storedProcedureName, string BaseFrameworkEntities, params SqlParameter[] p)
        { //获得返回集实例
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(_connectionStringBaseFramework))
            {

                SqlCommand comm = new SqlCommand();
                comm.CommandText = storedProcedureName;
                comm.CommandType = CommandType.StoredProcedure;
                //设置参数
                if (p != null && p.Length > 0)
                {
                    foreach (SqlParameter parameter in p)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        comm.Parameters.Add(parameter);
                    }
                }
                comm.Connection = conn;
                SqlDataAdapter da = new SqlDataAdapter(comm);
                //try
                //{
                //填充数据
                da.Fill(dt);
            }
            return dt;
        }

        public static DataTable ExecuteStoredProcedureByDSName(string storedProcedureName, string DataSourceName, params SqlParameter[] p)
        { //获得返回集实例
            DataTable dt = new DataTable();
            string commonConnectionString = "";
            if (DataSourceName == "WSJD_BaseFrameworkEntities")
            {
                commonConnectionString = _connectionStringBaseFramework;
            }
            else if (DataSourceName == "HIP_EHREntities")
            {
                commonConnectionString = _connectionString;
            }
            else if (DataSourceName == "HIP_FinanceEntities")
            {
                commonConnectionString = _connectionStringHIP_FinanceEntities;
            }
            using (SqlConnection conn = new SqlConnection(commonConnectionString))
            {

                SqlCommand comm = new SqlCommand();
                comm.CommandText = storedProcedureName;
                comm.CommandType = CommandType.StoredProcedure;
                //设置参数
                if (p != null && p.Length > 0)
                {
                    foreach (SqlParameter parameter in p)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        comm.Parameters.Add(parameter);
                    }
                }
                comm.Connection = conn;
                SqlDataAdapter da = new SqlDataAdapter(comm);
                //try
                //{
                //填充数据
                da.Fill(dt);
            }
            return dt;
        }


        /// <summary>
        /// 根据数据源名称返回DataSet
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="DataSourceName"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static DataSet ExecuteStoredProcedureByDSNameEx(string storedProcedureName, string DataSourceName, params SqlParameter[] p)
        { //获得返回集实例
            DataSet ds = new DataSet();
            string commonConnectionString = "";
            if (DataSourceName == "BaseFrameworkEntities")
            {
                commonConnectionString = _connectionStringBaseFramework;
            }
            else if (DataSourceName == "HIP_EHREntities")
            {
                commonConnectionString = _connectionString;
            }
            else if (DataSourceName == "HIP_FinanceEntities")
            {
                commonConnectionString = _connectionStringHIP_FinanceEntities;
            }
            using (SqlConnection conn = new SqlConnection(commonConnectionString))
            {
                SqlCommand comm = new SqlCommand();
                comm.CommandText = storedProcedureName;
                comm.CommandType = CommandType.StoredProcedure;
                //设置参数
                if (p != null && p.Length > 0)
                {
                    foreach (SqlParameter parameter in p)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        comm.Parameters.Add(parameter);
                    }
                }
                comm.Connection = conn;
                SqlDataAdapter da = new SqlDataAdapter(comm);
                //填充数据
                da.Fill(ds);
            }
            return ds;
        }

        /// <summary>
        /// 执行存储过程(无输出参数),返回DataSet
        /// </summary>
        /// <param name="storedProcedureName">存储过程名</param>
        /// <param name="p">参数(允许0个或者0个以上)</param>
        public static DataSet ExecuteStoredProToDataSet(string storedProcedureName, params SqlParameter[] p)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(ExecuteStoredProcedure(storedProcedureName, p));
            return ds;
        }

        public static DataSet ExecuteStoredProToDataSet(string storedProcedureName, string BaseFrameworkEntities, params SqlParameter[] p)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(ExecuteStoredProcedure(storedProcedureName, BaseFrameworkEntities, p));
            return ds;
        }

        public static DataSet ExecuteStoredProToDataSetByDSName(string storedProcedureName, string DateSourceName, params SqlParameter[] p)
        {
            return ExecuteStoredProcedureByDSNameEx(storedProcedureName, DateSourceName, p);
        }

        #endregion

        #region  执行 SQL 语句,返回DataTable
        /// <summary>
        /// 执行 SQL 语句
        /// </summary>
        /// <param name="sql">SQL 语句</param>
        /// <param name="p">参数集</param>
        /// <returns></returns>
        public static DataTable ExecuteSqlToTable(string sql, params SqlParameter[] p)
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = sql;

            return ExecuteSearch(comm, p);
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="SQLText">sql语句</param>
        /// <param name="DataSourceName">数据源名称</param>
        /// <param name="cmdType">SqlCommand.CommandText属性 若要执行存储过程传"StoredProcedure"否则传空字符串即可</param>
        /// <param name="p">参数(允许0个或者0个以上)</param>
        /// <returns></returns>
        public static DataTable ExecuteSql(string SQLString, string DataSourceName, string cmdType, params SqlParameter[] p)
        {
            //获得返回集实例
            DataTable dt = new DataTable();
            string commonConnectionString = "";
            if (DataSourceName == "BaseFrameworkEntities")
            {
                commonConnectionString = _connectionStringBaseFramework;
            }
            else if (DataSourceName == "HIP_EHREntities")
            {
                commonConnectionString = _connectionString;
            }
            else if (DataSourceName == "HIP_FinanceEntities")
            {
                commonConnectionString = _connectionStringHIP_FinanceEntities;
            }
            using (SqlConnection conn = new SqlConnection(commonConnectionString))
            {
                SqlCommand comm = new SqlCommand();
                comm.CommandText = SQLString;
                if (cmdType == "StoredProcedure")
                    comm.CommandType = CommandType.StoredProcedure;
                //设置参数
                if (p != null && p.Length > 0)
                {
                    foreach (SqlParameter parameter in p)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        comm.Parameters.Add(parameter);
                    }
                }
                comm.Connection = conn;
                SqlDataAdapter da = new SqlDataAdapter(comm);
                //填充数据
                da.Fill(dt);
            }
            return dt;
        }

        #endregion

        #region  执行 SQL 语句,返回DataSet
        /// <summary>
        /// 执行 SQL 语句,返回DataSet
        /// </summary>
        /// <param name="sql">SQL 语句</param>
        /// <param name="p">参数集</param>
        /// <returns></returns>
        public static DataSet ExecuteSqlToDataSet(string sql, params SqlParameter[] p)
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = sql;
            DataSet ds = new DataSet();
            ds.Tables.Add(ExecuteSearch(comm, p));
            return ds;
        }

        public static DataSet ExecuteSqlToDataSetByList(string sql, List<SqlParameter> Plist)
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = sql;
            DataSet ds = new DataSet();
            SqlParameter[] p = new SqlParameter[Plist.Count];

            Plist.CopyTo(p);

            ds.Tables.Add(ExecuteSearch(comm, p));
            return ds;
        }

        #endregion

        #region 执行Command语句
        /// <summary>
        /// 执行Command语句
        /// </summary>
        /// <param name="comm">执行的命令</param>
        /// <param name="p">参数集</param>
        /// <returns>查询的结果集</returns>
        private static DataTable ExecuteSearch(SqlCommand comm, params SqlParameter[] p)
        {
            //获得返回集实例
            DataTable dt = new DataTable();
            //创建连接
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                //设置参数
                if (p != null && p.Length > 0)
                {
                    foreach (SqlParameter parameter in p)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        comm.Parameters.Add(parameter);
                    }
                }
                comm.Connection = conn;
                SqlDataAdapter da = new SqlDataAdapter(comm);
                //try
                //{
                //填充数据
                da.Fill(dt);
                comm.Parameters.Clear();
                //关闭连接
                conn.Close();

            }
            return dt;
        }

        #endregion

        /// <summary>
        /// 通用分页
        /// </summary>
        /// <param name="Sql">构造完成的SQL字符串</param>
        /// <param name="PageIndex">页数</param>
        /// <param name="PageSize">页大小</param>
        /// <param name="OrderBy">排序字段 比如 ： ID DESC </param> 
        /// <param name="Paras">参数集</param>
        /// <param name="TotalCount">返回总记录数</param>
        /// <returns>datatable</returns>
        public static DataTable CreateSqlByPageExcuteSql(string Sql, int PageIndex, int PageSize, string OrderBy, List<SqlParameter> Paraslist, ref int TotalCount)
        {
            StringBuilder sbSql = new StringBuilder();
            if (PageIndex == 1)
                sbSql.Append("SELECT TOP " + PageSize + " *  FROM");
            else
                sbSql.Append("SELECT * FROM ");
            sbSql.Append(" ( SELECT ROW_NUMBER() OVER (ORDER BY " + OrderBy + ") as RowNumber,tempTable.*");
            sbSql.Append(" FROM ( " + Sql + " ) AS  tempTable ) AS tmp ");

            if (PageIndex != 1)
                sbSql.Append("WHERE RowNumber BETWEEN CONVERT(varchar,(@PageIndex-1)*@PageSize+1) AND CONVERT(varchar,(@PageIndex-1)*@PageSize+@PageSize) ");

            sbSql.Append(" SELECT @TotalRecord=count(*) from (" + Sql + ") tempTable");

            /*重新构造SqlParameter*/
            int index = 0;
            int Length = 0;
            SqlParameter[] SqlParas;

            if (Paraslist != null && Paraslist.Count > 0)
            {
                Length = Paraslist.Count;
                SqlParas = new SqlParameter[Length + 3];
                for (int i = 0; i < Paraslist.Count; i++)
                {
                    SqlParas[i] = Paraslist[i];
                    index++;
                }
            }
            else
                SqlParas = new SqlParameter[Length + 3];



            /*将分页参数追加至SqlParameter*/
            SqlParas[index] = new SqlParameter("@PageIndex", SqlDbType.Int);
            SqlParas[index].Value = PageIndex;
            index++;
            SqlParas[index] = new SqlParameter("@PageSize", SqlDbType.Int);
            SqlParas[index].Value = PageSize;
            index++;
            SqlParas[index] = new SqlParameter("@TotalRecord", SqlDbType.Int);
            SqlParas[index].Direction = ParameterDirection.Output;
            DataTable dtTemp = ExecuteSqlToTable(sbSql.ToString(), SqlParas);
            TotalCount = (int)SqlParas[index].Value;
            return dtTemp;
        }
    }
}
